package jdbc;

import org.junit.Test;

import javax.sql.rowset.serial.SerialBlob;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestUpdate {

    @Test
    public void insert() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String sql = "INSERT INTO user (name, age, email, phone) VALUES (?,?,?,?)";

        try {
            conn = ConnUtil.getConnection();
            pstmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);

            pstmt.setString(1, "lili");
            pstmt.setInt(2, 20);
            pstmt.setString(3, "vdsvd@dwq");
            pstmt.setString(4, "562165");

            int sum = pstmt.executeUpdate();
            System.out.println(sum + "行受到影响");
            ResultSet rs = pstmt.getGeneratedKeys();
            if (rs.next()) {
                int pkey = rs.getInt(1);
                System.out.println("自增ID " + pkey);
            }

        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            ConnUtil.close(null, pstmt, conn);
        }

    }



    @Test
    public void update() {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = " UPDATE `jdbc`.`user` SET `name` = ? , `age` = ? WHERE `id` = ? ";

        try {
            conn = ConnUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, "tx");
            stmt.setInt(2, 24);
            stmt.setInt(3, 1);
            stmt.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            ConnUtil.close(null, stmt, conn);
        }

    }




    @Test
    public void delete() {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = " DELETE FROM `jdbc`.`user` WHERE `id` = ? ";

        try {
            conn = ConnUtil.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, 3);
            stmt.executeUpdate();

        }
        catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        finally {
            ConnUtil.close(null, stmt, conn);
        }

    }



    @Test
    public void batch() {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            String sql = "INSERT INTO `jdbc`.`user`(`name`, `age`, `email`, `phone`) VALUES (?, ?, ?, ?)";
            conn = ConnUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < 1000; i++) {
                pstmt.setString(1, "user" + i);
                pstmt.setInt(2, i);
                pstmt.setString(3, i+ "@gmail.com");
                pstmt.setString(4, i+ "1565516");
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        finally {
            ConnUtil.close(null, pstmt, conn);
        }
    }



    @Test
    public void saveBlob() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        FileInputStream inputStream = null;
        String sql = "INSERT INTO data (name, data) VALUES (?,?)";

        try {
            inputStream = new FileInputStream("D:\\新建文件夹\\02.mp4");
            byte bytes[] = new byte[inputStream.available()];
            inputStream.read(bytes);
            Blob blob = new SerialBlob(bytes);

            conn = ConnUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "02.mp4");
            pstmt.setBlob(2, blob);
            pstmt.executeUpdate();
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        finally {
            ConnUtil.IOUtil.close(inputStream, null);
            ConnUtil.close(null, pstmt, conn);
        }
    }




    @Test
    public void openBlob() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        InputStream inputStream = null;
        OutputStream outputStream = null;
        String sql = "SELECT * FROM data";

        try {
            conn = ConnUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rset = pstmt.executeQuery();
            while (rset.next()) {
                Blob blob = rset.getBlob("data");
                String name = rset.getString("name");
                inputStream = blob.getBinaryStream();
                outputStream = new FileOutputStream("d:\\1\\" + name);
                byte buf[] = new byte[1024];
                int buflen = 0;
                while ((buflen = inputStream.read(buf, 0, buf.length)) != -1) {
                    outputStream.write(buf, 0, buflen);
                    outputStream.flush();
                }
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        finally {
            ConnUtil.IOUtil.close(inputStream, outputStream);
            ConnUtil.close(rset, pstmt, conn);
        }
    }


}





